MySQL 全览

本文基于 MySQL 8.0 官方文档梳理，覆盖总体架构、核心组件、SQL 执行流程、InnoDB 存储引擎、索引、事务与锁、主从复制、备份恢复，最后给出实战演示操作手册与参数指令速查手册。

一、总体架构

分层架构

MySQL 采用 可插拔存储引擎 (Pluggable Storage Engine) 架构：上层的服务器层（Server Layer）统一负责连接管理、SQL 解析、优化与执行调度，底层通过统一的存储引擎接口调用不同的存储引擎完成实际的数据读写。数据字典、索引、事务等由各个存储引擎自行实现。

图 1　MySQL 总体架构组件图（结构图）

# 查看当前服务器支持的存储引擎
SHOW ENGINES\G

-- InnoDB     : 默认引擎，支持事务、行级锁、外键
-- MyISAM     : 表级锁，适合只读/读多写少场景
-- MEMORY     : 数据全放内存，适合快速查找
-- CSV        : 表即 CSV 文本文件
-- ARCHIVE    : 紧凑无索引，适合归档

部署图

一个 MySQL 实例即一个 mysqld 进程。典型部署包括：单机部署、主从部署（一主多从，读写分离）、多主/级联等。客户端（应用或 CLI）通过 TCP 3306 端口与 mysqld 通信。

图 2　MySQL 典型部署图（结构图）

连接方式

二、核心组件

连接管理与线程模型

每个客户端连接在服务器中对应一个连接对象。MySQL 8.0 为每个连接分配一个线程，并通过 thread_cache_size 缓存线程以便复用。连接建立后先做身份认证与权限校验（基于 mysql 库的 user / db / tables_priv 等授权表）。

专用名词解释：连接池 (Connection Pool)——为减少每次新建连接的开销，在客户端（应用侧）或服务器侧缓存已建立的连接；会话 (Session)——一次连接内持续的上下文，包含系统变量、临时表、事务状态等。

SQL 接口与解析器

• SQL 接口：接收客户端发来的 SQL 语句，包括 DML、DDL、存储过程、视图等。
• 解析器 (Parser)：对 SQL 进行词法分析（Lexer）与语法分析（Grammar），生成语法树 (Parse Tree)，并做语义检查。
• 查询缓存 (Query Cache)：MySQL 8.0 已完全移除（5.7 默认关闭）。

优化器与执行器

• 优化器 (Optimizer)：对语法树做逻辑优化（等价变换、外连接转换等）与物理优化（选择访问路径、连接顺序、索引选择），生成执行计划。代价估算基于统计信息（information_schema.statistics 与表统计）。
• 执行器 (Executor)：按照执行计划逐行调用存储引擎接口，返回结果集；负责查询结果的排序、聚合、连接运算等。涉及权限再次校验（对具体表的访问权限）。

存储引擎接口

服务器层通过 handler（存储引擎 API）与引擎交互。每个引擎以动态库方式可插拔：ha_innodb、ha_myisam 等。存储引擎负责：事务支持、行级/表级锁、MVCC、索引（B+树/哈希等）、缓存策略与物理存储格式。

专用名词解释：Handler——MySQL 服务器层与存储引擎之间的接口抽象层，一个表对应一个 handler 实例；Data Dictionary（数据字典）——8.0 中用 InnoDB 存储系统表（mysql 库）来保存库、表、列、索引等元数据。

三、SQL 执行流程

活动图

一条 SELECT 从客户端发出到返回结果，依次经过：连接器（认证+权限）→ 解析器 → 优化器 → 执行器 → 存储引擎。写语句（UPDATE/DELETE/INSERT）还需经过 binlog 与 redo log 的两阶段提交以保证崩溃一致。

图 3　SQL 语句执行活动图（行为图）

-- 用 EXPLAIN 观察优化器选择的执行计划
EXPLAIN SELECT id, name FROM user WHERE age > 18 ORDER BY created_at DESC;
-- 输出中关注：type（访问类型）、key（实际使用的索引）、rows（扫描行数）、Extra（是否 Using index / Using filesort）

时序图

以一条 UPDATE 为例：事务修改一行时，先在 Buffer Pool 中定位并修改内存页，同时写 undo log（回滚）、redo log（崩溃恢复），并记录 binlog。提交时通过两阶段提交保证 redo log 与 binlog 一致性。

图 4　一次 UPDATE 事务执行时序图（行为图）

-- 模拟时序图核心动作的两阶段提交：
START TRANSACTION;
UPDATE user SET balance = balance - 100 WHERE id = 1;   -- 内存改页 + undo + redo
UPDATE user SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- redo log prepare -> 写 binlog -> redo log commit

四、存储引擎

引擎对比与选择

专用名词解释：ACID——原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability) 四项事务特性；MVCC (Multi-Version Concurrency Control)——多版本并发控制，通过保留行的历史版本来实现非阻塞一致性读。

InnoDB 内存结构

• Buffer Pool（缓冲池）：缓存数据页与索引页，极大减少磁盘 I/O，独占内存建议设到物理内存的 70%~80%。
• Change Buffer（变更缓冲）：缓存对二级索引页的修改，避免每次修改都读盘，后台异步合并。
• Adaptive Hash Index（自适应哈希索引）：基于访问频率自动为 B+ 树热点页建立哈希索引，加速等值查找。
• Log Buffer（日志缓冲）：暂存 redo log 记录，定期刷盘。

InnoDB 磁盘结构

• 系统表空间 (ibdata1)：8.0 中存放数据字典与回滚段（undo）。
• 独立表空间 (.ibd)：每张表的表数据与索引，默认开启每表一个文件（innodb_file_per_table）。
• Redo Log（重做日志，#innodb_redo 目录）：记录物理变更，用于崩溃恢复，循环写入。
• Undo Log（回滚日志）：记录行的旧版本，用于回滚与 MVCC 快照读。
• Doublewrite Buffer（双写缓冲）：写数据页前先写双写文件，防止页写入一半时崩溃导致页损坏。
• Binlog（二进制日志，服务器层）：记录逻辑变更，用于主从复制与基于时间点恢复。

图 5　InnoDB 内存与磁盘结构图（结构图）

# 查看 InnoDB 关键状态
SHOW ENGINE INNODB STATUS\G      -- 事务、锁、Buffer Pool、I/O 等综合信息
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';  -- 逻辑读
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';          -- 物理读
# 命中率 ≈ 1 - 物理读/逻辑读，目标 99% 以上

Buffer Pool 与 LRU

Buffer Pool 以页（默认 16KB）为单位，用改进的 LRU（最近最少使用） 算法管理：链表被分为头部 young 区（约 5/8）与尾部 old 区（约 3/8）。新读入的页先插入 old 区头部（midpoint），只有再次被访问才会晋升到 young 区，从而避免全表扫描一次性把热数据挤出去。

专用名词解释：LRU (Least Recently Used)——最近最少使用淘汰算法；Read-Ahead（预读）——InnoDB 预测将要用到的相邻页，提前异步读入 Buffer Pool。

Redo Log 与崩溃恢复

每次数据页修改时，InnoDB 把变更记录追加写入 redo log（先入 Log Buffer，再按 innodb_flush_log_at_trx_commit 策略刷盘）。服务器异常退出后重启时，从最近一次 checkpoint 对应的 LSN（Log Sequence Number） 开始回放 redo log，把未落盘但已提交的事务重做一遍；未提交的事务则依据 undo log 回滚。此机制保证了 持久性(Durability) 与 原子性(Atomicity)。

# 8.0.30+ 动态调整 redo log 容量
SET GLOBAL innodb_redo_log_capacity = 8589934592;  -- 8GB
# 查看 redo log 文件与 LSN 区间
SELECT FILE_NAME, START_LSN, END_LSN FROM performance_schema.innodb_redo_log_files;

五、索引

B+ 树结构

InnoDB 的索引使用 B+ 树：非叶节点只存索引键（用于路由），叶子节点存数据或主键指针，所有叶子节点通过双向链表串起来，天然支持范围扫描与有序遍历。树高一般为 2~4 层，意味着即使几百万行数据，查询也只需 2~4 次磁盘 I/O。

图 6　B+ 树索引结构图（结构图）

聚簇索引与二级索引

• 聚簇索引 (Clustered Index)：主键即聚簇索引，叶子节点直接存放整行数据，表数据按主键物理排序。无主键时 InnoDB 使用第一个唯一非空索引，仍无则生成隐藏 rowid。
• 二级索引 (Secondary Index)：叶子节点存放索引键 + 主键值；通过二级索引查询时先找到主键，再回聚簇索引取整行数据（回表）。
• 覆盖索引 (Covering Index)：所需列全部包含在某二级索引中，无需回表，用 Extra: Using index 标识。

专用名词解释：回表 (Table Lookup)——先扫二级索引拿到主键，再按主键到聚簇索引取完整行的过程；最左前缀 (Leftmost Prefix)——联合索引 (a,b,c) 可被 a、a,b、a,b,c 查询利用，条件需从最左列开始连续匹配。

索引类型与使用原则

-- 索引使用原则：
-- 1. 等值+范围：联合索引把等值列放前面，范围列放后面，如 INDEX(age, created_at)
-- 2. 覆盖查询避免回表：SELECT name,age WHERE age=? 建 INDEX(age, name)
-- 3. 不要在索引列上做函数运算或隐式类型转换（会失效索引）
-- 4. 选择性（去重比例）高的列优先建索引

六、事务与隔离级别

事务状态机

InnoDB 事务的生命周期：ACTIVE（执行中）→ PARTIALLY COMMITTED → COMMITTED（已提交）/ 或 ROLLED BACK（回滚）；发生死锁等被回滚后结束。可从 performance_schema 的 events_transactions_current 观察。

图 7　InnoDB 事务状态机图（行为图）

四种隔离级别

SQL:1992 标准定义的四种隔离级别，InnoDB 默认 REPEATABLE READ。从宽松到严格：READ UNCOMMITTED（脏读）、READ COMMITTED（不可重复读）、REPEATABLE READ（幻读）、SERIALIZABLE（完全串行）。

专用名词解释：脏读 (Dirty Read)——读到其他事务未提交的数据；不可重复读——同一事务内两次读同一行结果不同（其他事务修改并提交）；幻读 (Phantom Row)——同一事务内两次范围查询返回行数不同（其他事务插入新行）。

MVCC 实现

InnoDB 每行数据携带隐藏列 DB_TRX_ID（最近修改事务 id）与 DB_ROLL_PTR（undo 版本链指针）。事务第一次读时生成 Read View（当前活跃事务 id 列表）。一致性快照读时沿 undo 链找到第一个满足「trx_id < 最小活跃 id」或「已提交」的版本返回。这就是"快照读不用加锁"的原因。

-- 会话 A（保持事务打开）
START TRANSACTION;
SELECT balance FROM user WHERE id=1;   -- 建立快照，读到 100

-- 会话 B 提交修改
UPDATE user SET balance=200 WHERE id=1; COMMIT;

-- 会话 A 再次读，仍是 100（快照读，REPEATABLE READ）
SELECT balance FROM user WHERE id=1;   -- 100

锁机制

InnoDB 锁是行级锁 + 意向表锁的组合：

专用名词解释：意向锁 (Intention Lock)——表级锁，用来快速判断一个表是否可能被行锁冲突，避免加表锁时扫描全表行锁；Next-Key Lock——「记录锁 + 该记录前一个间隙的锁」的组合，用于 RR 级别防幻读。

死锁

两个事务相互持有对方需要的锁时产生死锁。InnoDB 会自动检测死锁：回滚其中一个事务（报 ERROR 1213 Deadlock found）并释放其锁，让另一个继续。减小死锁：保持一致的加锁顺序、让事务尽量短小、使用 READ COMMITTED 减少间隙锁、必要时对热点行使用串行化策略。

# 查看最近一次死锁详情
SHOW ENGINE INNODB STATUS\G   -- 看 LATEST DETECTED DEADLOCK 段

Spring 事务传播机制（应用层）

事务传播（Transaction Propagation）是 Spring 框架的概念，MySQL 本身没有。它描述当方法嵌套调用、且内外方法都被 @Transactional 标记时，内层方法的事务如何与当前已有事务协同。核心由 @Transactional(propagation = ...) 控制，默认 REQUIRED。

图 9　Spring 事务传播行为对比时序图（行为图，应用层）

// 典型用法（Spring Boot / Java）
@Transactional(propagation = Propagation.REQUIRED)      // 默认：参与外层事务
public void transfer() { debit(); credit(); }

@Transactional(propagation = Propagation.REQUIRES_NEW)  // 独立提交，不受外层回滚影响
public void writeAuditLog() { ... }

@Transactional(propagation = Propagation.NESTED)        // Savepoint 子事务
public void processOne() { ... }

重要提醒：Spring 事务默认基于 动态代理（AOP），因此同类内部调用（this.method()）不会经过代理，传播/回滚注解失效；需要自注入或拆分到其他 Bean。REQUIRES_NEW 与 NESTED 的差异在于：REQUIRES_NEW 是完全独立的物理事务（内外可分别提交），NESTED 是逻辑子事务（回滚点 Savepoint，最外层整体提交时才会真正提交）。

七、主从复制

复制时序图

MySQL 复制为异步机制：主库（source）将变更写入 binlog；从库（replica）的 I/O 线程连接主库读取 binlog 事件写入本地 relay log（中继日志）；从库的 SQL 线程（或并行 worker）读取 relay log 并重放到自己的数据中。

图 8　主从复制时序图（行为图）

-- 从库查看复制状态（MySQL 8.0.22+ 用 SHOW REPLICA STATUS，旧版用 SHOW SLAVE STATUS）
SHOW REPLICA STATUS\G
-- 关注：Replica_IO_Running / Replica_SQL_Running 应为 Yes
--       Seconds_Behind_Source 为延迟秒数（0 表示无延迟）

专用名词解释：Binlog Dump 线程——主库上为每个从库连接创建、发送 binlog 的线程；I/O 接收线程——从库拉取并写 relay log 的线程；SQL 应用线程——从库执行 relay log 事件的线程（多线程复制时含一个协调线程 + N 个 worker）。

复制格式

GTID 复制

GTID（Global Transaction Identifier） 为每个已提交事务分配全局唯一标识 source_uuid:transaction_id。GTID 复制不依赖 binlog 文件名与位置，天然避免重复应用同一事务，主从切换、故障恢复更简单。在 MySQL 8.0 中推荐启用。

# 启用 GTID（my.cnf）
gtid_mode = ON
enforce_gtid_consistency = ON

# 从库建立 GTID 复制
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.5', SOURCE_PORT=3306,
  SOURCE_USER='repl', SOURCE_PASSWORD='xxx',
  SOURCE_AUTO_POSITION = 1;
START REPLICA;

八、备份与恢复

备份策略分为逻辑备份与物理备份，并结合 binlog 做时间点恢复（PITR）。

# 典型恢复流程：
# 1) 恢复最近一次全量备份
mysql -u root -p < db.sql
# 2) 回放该备份之后到故障前的 binlog
mysqlbinlog --start-position=154 --stop-datetime="2026-08-22 10:00:00" binlog.000010 | mysql -u root -p

专用名词解释：PITR (Point-In-Time Recovery)——时间点恢复，用 binlog 把数据库恢复到某个精确时刻，用于误操作回滚。

九、实战演示操作手册

安装与初始化

# macOS (Homebrew)
brew install mysql
brew services start mysql
mysql_secure_installation        # 初始化 root 密码、移除匿名用户等

# Debian/Ubuntu
apt install mysql-server
systemctl start mysql
mysql_secure_installation

# 验证版本
mysql --version        # mysql  Ver 8.0.x for macos13 on x86_64 ...

连接与用户管理

mysql -uroot -p                      # 本地 socket 连接
mysql -h 10.0.0.5 -P3306 -uapp -p    # 远程 TCP 连接

# 创建用户并授权
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass!2026';
CREATE USER 'dev'@'10.0.0.%' IDENTIFIED BY 'DevPass123';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'%';
GRANT ALL PRIVILEGES ON mydb.* TO 'dev'@'10.0.0.%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

# 查看用户与权限
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'app'@'%';

建库建表

CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mydb;

CREATE TABLE user (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  name       VARCHAR(64)     NOT NULL COMMENT '姓名',
  email      VARCHAR(128)    NOT NULL COMMENT '邮箱',
  age        TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
  balance    DECIMAL(12,2)   NOT NULL DEFAULT 0 COMMENT '余额',
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_email (email),
  KEY idx_age_created (age, created_at)
) ENGINE=InnoDB COMMENT='用户表';

-- 查看表结构
SHOW CREATE TABLE user\G
DESC user;

增删改查 CRUD

-- 插入
INSERT INTO user (name, email, age, balance) VALUES
('张三','zhang@example.com',25,100.00),
('李四','li@example.com',30,200.50);

-- 多行/忽略冲突
INSERT INTO user (id, name, email) VALUES (1,'王五','wang@example.com')
  ON DUPLICATE KEY UPDATE name=VALUES(name);

-- 查询
SELECT id, name, age FROM user WHERE age > 20 ORDER BY created_at DESC LIMIT 10;
SELECT COUNT(*), AVG(age) FROM user WHERE balance > 0;

-- 更新
UPDATE user SET balance = balance - 100 WHERE id = 1;

-- 删除
DELETE FROM user WHERE id = 9;
TRUNCATE TABLE user;   -- 清空表（不能回滚，重置自增）

索引实战

-- 建索引 / 删索引
ALTER TABLE user ADD INDEX idx_age (age);
CREATE INDEX idx_email_prefix ON user (email(10));   -- 前缀索引
DROP INDEX idx_age ON user;

-- EXPLAIN 验证索引使用
EXPLAIN SELECT name FROM user WHERE age = 25 AND created_at > '2026-01-01';
-- 联合索引 idx_age_created 生效，避免回表

-- 失效索引的典型反例
SELECT * FROM user WHERE age + 1 > 26;              -- 列上运算，失效
SELECT * FROM user WHERE age > 26;                  -- 正确写法

事务与锁实战

-- 事务基本用法
START TRANSACTION;
UPDATE user SET balance = balance - 50 WHERE id = 1;
UPDATE user SET balance = balance + 50 WHERE id = 2;
COMMIT;   -- 或 ROLLBACK;

-- 手动设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;   -- REPEATABLE-READ（默认）

-- 锁定读（悲观锁）
SELECT * FROM user WHERE id = 1 FOR UPDATE;    -- 排他锁
SELECT * FROM user WHERE id = 1 FOR SHARE;     -- 共享锁（8.0 语法）

-- 模拟死锁：两个会话按相反顺序锁 id=1 与 id=2，
-- 会话A锁1→等2，会话B锁2→等1，InnoDB 自动回滚其一

主从复制实战

# 主库 my.cnf
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

# 从库 my.cnf
server-id = 2
relay_log = /var/log/mysql/mysql-relay

# 主库创建复制账号
CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'ReplPass123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';

# 从库建立并启动复制（GTID 方式）
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.5', SOURCE_USER='repl',
  SOURCE_PASSWORD='ReplPass123', SOURCE_AUTO_POSITION=1;
START REPLICA;

# 验证
SHOW REPLICA STATUS\G   -- IO/SQL 线程均 Yes，延迟 0

备份恢复实战

# 全量备份（InnoDB 用 --single-transaction 保证一致性快照）
mysqldump -uroot -p --single-transaction --master-data=2 --routines mydb > mydb_backup.sql

# 恢复
mysql -uroot -p < mydb_backup.sql

# 只恢复单表
mysql -uroot -p mydb < user_table.sql

# binlog 时间点恢复（误删后回放）
mysqlbinlog --start-datetime="2026-08-22 09:00:00" --stop-datetime="2026-08-22 09:30:00" \
  /var/log/mysql/mysql-bin.000010 | mysql -uroot -p

性能排查实战

# 1) 慢查询日志（my.cnf）
slow_query_log = ON
long_query_time = 1        # 超过 1 秒记录
# 查询慢日志
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

# 2) EXPLAIN 看执行计划
EXPLAIN ANALYZE SELECT name FROM user WHERE age=25;  -- 8.0 实测耗时

# 3) 查看当前连接
SHOW PROCESSLIST;   -- 看是否有大量 Sleep / 长事务

# 4) 常见性能指标
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

# 5) 优化三板斧：索引 -> 查询改写 -> 参数调优

十、参数与指令速查手册

mysqld 关键参数

常用 SQL 指令速查

诊断命令速查

SHOW VARIABLES LIKE 'innodb%';        -- 查看系统变量（配置）
SHOW GLOBAL STATUS LIKE 'Innodb%';    -- 查看状态变量（运行时指标）
SHOW PROCESSLIST;                     -- 当前连接/线程
SHOW ENGINE INNODB STATUS\G           -- InnoDB 内部状态（事务/锁/Buffer Pool）
SHOW REPLICA STATUS\G                 -- 复制状态
SHOW MASTER STATUS;                   -- 主库 binlog 位置（8.0 别名 SHOW BINARY LOG STATUS）
SHOW BINARY LOGS;                     -- binlog 文件列表
SHOW CREATE TABLE t\G                 -- 表 DDL
SELECT @@hostname, @@port, @@version; -- 实例基本信息

-- information_schema：元数据
SELECT * FROM information_schema.tables WHERE table_schema='mydb';
SELECT * FROM information_schema.statistics WHERE table_schema='mydb' AND table_name='user';

-- performance_schema：性能
SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;